# -*- coding: utf-8 -*-
from flask import Flask, render_template, request, redirect, flash
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import CSRFProtect

app =Flask(__name__)
CSRFProtect(app) # 一旦使用post、put、delete、patch时校验token

app.config["SECRET_KEY"] = "dsfdfdfs" #flash需要设置
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:000000@hadoop102:3306/library"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
# app.config["SQLALCHEMY_ECHO"] = True # 会在控制台打印输出sql语句

# db = SQLAlchemy(app, use_native_unicode="gb2312")
db = SQLAlchemy(app)

class Author(db.Model):
    __tablename__ = "authors"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))
    # 添加关系到一方
    books = db.relationship("Book",backref="book",lazy="dynamic")

class Book(db.Model):
    __tablename__ = "books"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))
    # 添加外键到多方
    author_id = db.Column(db.Integer,db.ForeignKey(Author.id))
    # author_id = db.Column(db.Integer,db.ForeignKey("author.id")) # 等价上面


#展示页面
@app.route('/')
def show_page():

    #查询数据库
    authors = Author.query.all()

    # 渲染到页面
    return render_template('library.html', authors=authors)

# 添加数据
@app.route("/add_data",methods=["POST"])
def add_data():
    # 1.获取提交数据
    author_name = request.form.get("author")
    book_name = request.form.get("book")

    # 1.1判断输入内容是否为空
    if not all([author_name,book_name]):
        flash("作者或书籍不为空")
        return redirect("/")

    # 2.根据作者信息，查询作者对象
    author = Author.query.filter(Author.name == author_name).first()

    # 3.判断作者是否存在
    if author:
        # 4.通过书籍名称查询书籍对象,获取该作者有没有写该书
        book = Book.query.filter(Book.name == book_name,Book.author_id == author.id).first()
        # 5.判断书籍对象是否存在
        if book:
            flash("该作者已经有了该书")
        else:
            # 创建书籍对象添加到数据库
            book = Book(name=book_name,author_id=author.id)
            db.session.add(book)
            db.session.commit()
    else:
        # 作者不存在，创建作者添加数据库。创建书籍添加数据库
        author = Author(name=author_name)
        db.session.add(author)
        db.session.commit()
        book = Book(name=book_name, author_id=author.id)
        db.session.add(book)
        db.session.commit()
    # 6.重定向到首页
    return redirect("/")


@app.route("/delete_book/<int:book_id>")
def delete_book(book_id):
    # 1.根据书籍编号取出书记对象
    book = Book.query.get(book_id)
    # 2.删除书籍
    db.session.delete(book)
    db.session.commit()
    # 3.重定向到页面显示
    return redirect("/")

@app.route("/delete_author/<int:author_id>")
def delete_author(author_id):
    # 1.根据书籍编号取出书记对象
    author = Author.query.get(author_id)
    # 2.删除书籍
    db.session.delete(author)
    db.session.commit()
    # 3.重定向到页面显示
    return redirect("/")





if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    # 添加测试数据库
    # 生成数据
    au1 = Author(name='老王')
    au2 = Author(name='老尹')
    au3 = Author(name='老刘')
    # 把数据提交给用户会话
    db.session.add_all([au1, au2, au3])
    # 提交会话
    db.session.commit()

    bk1 = Book(name='老王回忆录', author_id=au1.id)
    bk2 = Book(name='我读书少，你别骗我', author_id=au1.id)
    bk3 = Book(name='如何才能让自己更骚', author_id=au2.id)
    bk4 = Book(name='怎样征服美丽少女', author_id=au3.id)
    bk5 = Book(name='如何征服英俊少男', author_id=au3.id)
    # 把数据提交给用户会话
    db.session.add_all([bk1, bk2, bk3, bk4, bk5])
    # 提交会话
    db.session.commit()
    app.run(debug=True)